VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsFunds"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True

Option Explicit

Public Function InsertFund(ByVal nComCode As String, _
                            ByVal nComID As String, _
                            ByVal sFundNumber As String, _
                            ByVal sNewFundType As String, _
                            ByVal sNewReasonCode As String, _
                            ByVal sFundName As String, _
                            ByVal sContactPerson As String, _
                            ByVal sDialingCode As String, _
                            ByVal sPhoneNumber As String, _
                            ByVal sAddress1 As String, ByVal sAddress2 As String, ByVal sAddress3 As String, ByVal sAddress4 As String, ByVal sPostalCode As String, _
                            ByVal sCreatedBy As String, _
                            Optional ByVal nFundPAYERefNumber = "", _
                            Optional ByVal sFundAdminEmailAddress = "info@keytax.co.za") As String

    Dim cnInsertFund As ADODB.Connection
    
    Dim rsHolder As ADODB.recordSet
    Dim nSeqNo As Double
    Dim cmd As ADODB.Command
    
    Dim nFundID As Double
    
    Set cnInsertFund = New ADODB.Connection
    
    
    Set cmd = New ADODB.Command 'CreateObject("ADODB.COMMAND")
    
    With cnInsertFund
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsHolder = New ADODB.recordSet
    
    '-------------------------------------------
    Set cmd.ActiveConnection = cnInsertFund
    cmd.CommandText = "dir_insert_funds"
    cmd.CommandType = adCmdStoredProc
    
        cmd.Parameters.Append cmd.CreateParameter("com_code", adVarChar, adParamInput, 5, IIf(nComCode = "", Null, nComCode))
        
        cmd.Parameters.Append cmd.CreateParameter("com_id", adDouble, adParamInput, 10, IIf(nComID = "", Null, nComID))
        
        cmd.Parameters.Append cmd.CreateParameter("fund_number", adDouble, adParamInput, 11, IIf(sFundNumber = "", Null, sFundNumber))
        
        cmd.Parameters.Append cmd.CreateParameter("scod_fund_type", adVarChar, adParamInput, 2, IIf(sNewFundType = "", Null, sNewFundType))
        
        cmd.Parameters.Append cmd.CreateParameter("scod_fund_create_rsn", adVarChar, adParamInput, 2, IIf(sNewReasonCode = "", Null, sNewReasonCode))
        
        cmd.Parameters.Append cmd.CreateParameter("fund_name", adVarChar, adParamInput, 120, IIf(sFundName = "", Null, sFundName))
        
        cmd.Parameters.Append cmd.CreateParameter("contact_person", adVarChar, adParamInput, 120, IIf(sContactPerson = "", Null, sContactPerson))
        
        cmd.Parameters.Append cmd.CreateParameter("dialing_code", adVarChar, adParamInput, 10, IIf(sDialingCode = "", Null, sDialingCode))
        
        cmd.Parameters.Append cmd.CreateParameter("telephone_number", adVarChar, adParamInput, 10, IIf(sPhoneNumber = "", Null, sPhoneNumber))
        
        cmd.Parameters.Append cmd.CreateParameter("post_addr_l1", adVarChar, adParamInput, 35, IIf(sAddress1 = "", Null, sAddress1))
        
        cmd.Parameters.Append cmd.CreateParameter("post_addr_l2", adVarChar, adParamInput, 35, IIf(sAddress2 = "", Null, sAddress2))
        
        cmd.Parameters.Append cmd.CreateParameter("post_addr_l3", adVarChar, adParamInput, 35, IIf(sAddress3 = "", Null, sAddress3))
        
        cmd.Parameters.Append cmd.CreateParameter("post_addr_l4", adVarChar, adParamInput, 35, IIf(sAddress4 = "", Null, sAddress4))
        
        cmd.Parameters.Append cmd.CreateParameter("post_postal_code", adVarChar, adParamInput, 10, IIf(sPostalCode = "", Null, sPostalCode))
        
        cmd.Parameters.Append cmd.CreateParameter("created_by", adVarChar, adParamInput, 10, IIf(sCreatedBy = "", Null, sCreatedBy))
        
        cmd.Parameters.Append cmd.CreateParameter("fund_paye_ref_num", adDouble, adParamInput, 10, IIf(Len(nFundPAYERefNumber) = 0, Null, CDbl(nFundPAYERefNumber)))
        
        cmd.Parameters.Append cmd.CreateParameter("email_address", adVarChar, adParamInput, 50, IIf(Len(sFundAdminEmailAddress) = 0, Null, sFundAdminEmailAddress))
        
        cmd.Parameters.Append cmd.CreateParameter("fun_id", adVarChar, adParamOutput, 10, nFundID)
    
    cmd.Execute
    
    nFundID = cmd.Parameters("fun_id").Value
    
    InsertFund = nFundID

PROC_EXIT:
  InsertFund = nFundID
  Exit Function
  
PROC_ERROR:
  nSeqNo = 0
  Err.Raise Err.Number, "InsertFund", _
            "The fund could not be added." & vbCrLf & _
            "Description: " & Err.Description
  GoTo PROC_EXIT

'clean up
cnInsertFund.Close
Set cnInsertFund = Nothing
rsHolder.Close
Set rsHolder = Nothing

End Function






Public Function SplitFundTypeReasonCombo(ByVal sFundTypeReasonCombo As String, ByRef sFundType, ByRef sFundCreateReason) As String

sFundType = Left$(sFundTypeReasonCombo, 2)
sFundCreateReason = Right$(sFundTypeReasonCombo, 2)

End Function

Public Function FundExists(ByVal sUserName, ByVal nFundNumber) As String

    Dim cnFundExists As ADODB.Connection
    Dim rsFundExists As ADODB.recordSet
    Dim cmd
    
    
    Set cnFundExists = New ADODB.Connection
    
    Set cmd = New ADODB.Command
    Set rsFundExists = New ADODB.recordSet
    
    
    With cnFundExists
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With

    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnFundExists
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "Dir_get_fund_details"
    
    cmd.Parameters.Append cmd.CreateParameter("username", adVarChar, adParamInput, 10, sUserName)
    cmd.Parameters.Append cmd.CreateParameter("fund_number", adDouble, adParamInput, , CDbl(Val(nFundNumber)))
    
    ' Perform the query
    Set rsFundExists = cmd.Execute
        
    If Not rsFundExists.EOF Then
        FundExists = "true"
    Else
        FundExists = "false"
    End If

    'clean up
    rsFundExists.Close
    Set rsFundExists = Nothing
    cnFundExists.Close
    Set cnFundExists = Nothing

End Function


Public Function FundDupl(ByVal sUserName, ByVal sFundType, ByVal nFundNumber) As String

    Dim cnFundDupl As ADODB.Connection
    Dim rsFundDupl As ADODB.recordSet
    Dim cmd
    
    
    Set cnFundDupl = New ADODB.Connection
    
    Set cmd = New ADODB.Command
    Set rsFundDupl = New ADODB.recordSet
    
    
    With cnFundDupl
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With

    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnFundDupl
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "dir_get_fund_dups"
    
    cmd.Parameters.Append cmd.CreateParameter("username", adVarChar, adParamInput, 10, sUserName)
    cmd.Parameters.Append cmd.CreateParameter("fund_type", adVarChar, adParamInput, 2, sFundType)
    cmd.Parameters.Append cmd.CreateParameter("fund_number", adDouble, adParamInput, , CDbl(Val(nFundNumber)))
    
    ' Perform the query
    Set rsFundDupl = cmd.Execute
        
    If Not rsFundDupl.EOF Then
        FundDupl = "A fund with this number and type already exists.  <br><br>Please change either the fund number or the fund type."
    Else
        FundDupl = ""
    End If

    'clean up
    rsFundDupl.Close
    Set rsFundDupl = Nothing
    cnFundDupl.Close
    Set cnFundDupl = Nothing

End Function

'*-----------------------------------------------------------------------
'* Method:      GetFundTypeByID
'* Parameters:  strFundID - Fund ID
'* Returns:     Fund type for the specified fund
'* Description: This method will find the fund type for a specific fund, specified by FUND ID
'*-----------------------------------------------------------------------

Public Function GetFundTypeByID(ByVal nFundID As String) As String
    
    
    Dim cnFundType As ADODB.Connection
    Dim rsFundType As ADODB.recordSet
    Dim cmd
    
    
    Set cnFundType = New ADODB.Connection
    
    Set cmd = New ADODB.Command
    Set rsFundType = New ADODB.recordSet
    
    
    With cnFundType
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With

    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnFundType
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "Dir_get_Fund_ID_details"
    
    cmd.Parameters.Append cmd.CreateParameter("fun_id", adVarChar, adParamInput, 10, nFundID)
    
    Set rsFundType = cmd.Execute
        
    If Not rsFundType.EOF Then
        GetFundTypeByID = rsFundType!scod_fund_type
    Else
        GetFundTypeByID = "No Records Returned"
    End If
            
    'clean up
    rsFundType.Close
    Set rsFundType = Nothing
    cnFundType.Close
    Set cnFundType = Nothing
        
End Function


'*-----------------------------------------------------------------------
'* Method:      GetFundTypeMeaning
'* Parameters:  sFundTypeCode
'* Returns:     Recordset from stored procedure
'* Description: Gets all fund types for a specific form
'*-----------------------------------------------------------------------

Public Function GetFundTypeMeaning(ByVal sFormType As String, ByVal sFundTypeCode As String) As String

    Dim cnGetFundTypeMeaning As ADODB.Connection
    Dim rsGetFundTypeMeaning As ADODB.recordSet
    
    Dim sFundTypesString
    
    
    Dim cmd As ADODB.Command
    
    
    
    Set cnGetFundTypeMeaning = New ADODB.Connection
    With cnGetFundTypeMeaning
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsGetFundTypeMeaning = New ADODB.recordSet
    
    Set cmd = CreateObject("ADODB.COMMAND")
    
    cmd.CommandText = "dir_get_fund_types"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = cnGetFundTypeMeaning
    
        cmd.Parameters.Append cmd.CreateParameter("form_type", adVarChar, adParamInput, 10, sFormType)
    
    On Error Resume Next
    
    Set rsGetFundTypeMeaning = cmd.Execute
    
    rsGetFundTypeMeaning.MoveFirst
    
    sFundTypesString = ""
    
    While Not rsGetFundTypeMeaning.EOF
        If sFundTypeCode = rsGetFundTypeMeaning!fund_type_code Then
            sFundTypesString = rsGetFundTypeMeaning!fund_type_descr
        Else
            sFundTypesString = "Fund type code not valid"
        End If
      
    rsGetFundTypeMeaning.MoveNext

    Wend


    
    Select Case Err.Number
        Case 0
            GetFundTypeMeaning = sFundTypesString
        Case Else
            GetFundTypeMeaning = "WARNING: An error occured <br><br><b>Error Information</b> <br><br> Error Number: " & Err.Number & " <br><br> Error description: <br>" & Err.Description & ""
    End Select
    
    'clean up
    rsGetFundTypeMeaning.Close
    Set rsGetFundTypeMeaning = Nothing
    cnGetFundTypeMeaning.Close
    Set cnGetFundTypeMeaning = Nothing


End Function


Public Function UpdateFundDetails(ByVal nFundNumber As String, _
                         ByVal nFundID As String, _
                         ByVal sFundType As String, _
                         ByVal sCreateReason As String, _
                         ByVal sFundName As String, _
                         ByVal sContactPerson As String, _
                         ByVal sDialingCode As String, _
                         ByVal sPhoneNumber As String, _
                         ByVal sAddress1 As String, _
                         ByVal sAddress2 As String, _
                         ByVal sAddress3 As String, _
                         ByVal sAddress4 As String, _
                         ByVal sPostalCode As String, _
                         Optional ByVal nFundPAYERefNum As String = "", _
                         Optional ByVal sFundAdminEmailAddress As String = "info@keytax.co.za") As String

    Dim cnUpdateFundDetails As ADODB.Connection
    Dim cmd
    
    
    Set cnUpdateFundDetails = New ADODB.Connection
    
    Set cmd = New ADODB.Command
    
    With cnUpdateFundDetails
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With

    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnUpdateFundDetails
    
    'If the parameter was not specified, clear it
    If IsMissing(nFundPAYERefNum) Then
        nFundPAYERefNum = ""
    End If
    
    cmd.CommandText = "dir_update_fund_details"
    cmd.CommandType = adCmdStoredProc
    
        cmd.Parameters.Append cmd.CreateParameter("fund_number", adDouble, adParamInput, , IIf(nFundNumber = "", Null, nFundNumber))
        
        cmd.Parameters.Append cmd.CreateParameter("fun_id", adDouble, adParamInput, , IIf(nFundID = "", Null, nFundID))
        
        cmd.Parameters.Append cmd.CreateParameter("scod_fund_type", adVarChar, adParamInput, 2, IIf(sFundType = "", Null, sFundType))
        
        cmd.Parameters.Append cmd.CreateParameter("scod_fund_create_rsn", adVarChar, adParamInput, 2, IIf(sCreateReason = "", Null, sCreateReason))
        
        cmd.Parameters.Append cmd.CreateParameter("fund_name", adVarChar, adParamInput, 120, IIf(sFundName = "", Null, sFundName))
        
        cmd.Parameters.Append cmd.CreateParameter("contact_person", adVarChar, adParamInput, 120, IIf(sContactPerson = "", Null, sContactPerson))
        
        cmd.Parameters.Append cmd.CreateParameter("dialing_code", adVarChar, adParamInput, 10, IIf(sDialingCode = "", Null, sDialingCode))
        
        cmd.Parameters.Append cmd.CreateParameter("telephone_number", adVarChar, adParamInput, 10, IIf(sPhoneNumber = "", Null, sPhoneNumber))
        
        cmd.Parameters.Append cmd.CreateParameter("post_addr_l1", adVarChar, adParamInput, 35, IIf(sAddress1 = "", Null, sAddress1))
        
        cmd.Parameters.Append cmd.CreateParameter("post_addr_l2", adVarChar, adParamInput, 35, IIf(sAddress2 = "", Null, sAddress2))
        
        cmd.Parameters.Append cmd.CreateParameter("post_addr_l3", adVarChar, adParamInput, 35, IIf(sAddress3 = "", Null, sAddress3))
        
        cmd.Parameters.Append cmd.CreateParameter("post_addr_l4", adVarChar, adParamInput, 35, IIf(sAddress4 = "", Null, sAddress4))
        
        cmd.Parameters.Append cmd.CreateParameter("post_postal_code", adVarChar, adParamInput, 10, IIf(sPostalCode = "", Null, sPostalCode))
        
        cmd.Parameters.Append cmd.CreateParameter("fund_paye_ref_num", adDouble, adParamInput, , IIf(nFundPAYERefNum = "", Null, nFundPAYERefNum))
        
        cmd.Parameters.Append cmd.CreateParameter("email_address", adVarChar, adParamInput, 50, IIf(Len(sFundAdminEmailAddress) = 0, Null, sFundAdminEmailAddress))
                                    
    cmd.Execute

PROC_EXIT:
  
  Exit Function
  
PROC_ERROR:
  
Err.Raise Err.Number, "GetDirectiveDetails", _
          "The information could not be retrieved." & vbCrLf & _
          "Description: " & Err.Description
GoTo PROC_EXIT

'clean up
cnUpdateFundDetails.Close
Set cnUpdateFundDetails = Nothing
End Function

'*-----------------------------------------------------------------------
'* Method:      ValFundTypeForForm
'* Parameters:  Form - Form type ie FORM_C
'*              XFundID - transfer fund's ID
'*              user - KeyTax system sUserName
'*              FundType - type of fund
'* Returns:     Nothing if validation passes, message if not
'* Description: This method will validate that the correct type of transfer fund is used for a form
'*-----------------------------------------------------------------------

Public Function ValFundType(ByVal sFormType As String, ByVal nTransferFundID As String) As String
    
    Dim cnValFundType As ADODB.Connection
    
    Dim cmd
    Dim sValid
    Dim sTransferFundType
    Dim sFormFundType
    
    Set cnValFundType = New ADODB.Connection
    
    Set cmd = New ADODB.Command
    
    sTransferFundType = GetFundTypeByID(nTransferFundID)
    
    With cnValFundType
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Dim rsFormFundTypes As ADODB.recordSet
    
    
'    'Johann (20100304): Changed to use select statement for XFER_FUND_TYPES instead
'Fikile(20100708): Uncomment the below code...
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnValFundType

    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "dir_get_fund_types"

    cmd.Parameters.Append cmd.CreateParameter("form_type", adVarChar, adParamInput, 10, sFormType)

    ' Get the allowed fund types for this form.
    Set rsFormFundTypes = cmd.Execute
    
    Dim SQLString As String
    
'Fikile (20100708): Commented this code outto use the proc above.
'    SQLString = "select"
'    SQLString = SQLString & " scod.value  fund_type_code, scod.meaning fund_type_descr"
'    SQLString = SQLString & " from dir_scod_allowed_values saval, dir_system_codes scod"
'    SQLString = SQLString & " where ("
'    SQLString = SQLString & "         saval.code = 'FORM_TYPE'"
'    SQLString = SQLString & "         and  saval.value = '" & sFormType & "'"
'    SQLString = SQLString & "         and saval.allowed_Code = 'XFER_FUND_TYPE'"
'    SQLString = SQLString & "       )"
'    SQLString = SQLString & " and (saval.allowed_code = scod.code and saval.allowed_value = scod.value)"
'    SQLString = SQLString & " and allowed_value = '" & sTransferFundType & "'"
'
'    Set rsFormFundTypes = cnValFundType.Execute(SQLString)
    
    'Johann (20100304): No loop necessary. just do EOF check
'    Do Until rsFormFundTypes.EOF
'        sFormFundType = rsFormFundTypes("fund_type_code")
'        If sFormFundType = sTransferFundType Then
'            sValid = "Y"
'            Exit Do 'jump out, no point in doing the rest of the loop if we have a positive
'        End If
'    rsFormFundTypes.MoveNext
'    Loop

    If Not rsFormFundTypes.EOF Then
        sValid = "Y"
    Else
        sValid = "N"
    End If
    
    ValFundType = sValid
    
    'clean up
    cnValFundType.Close
    Set cnValFundType = Nothing
    Set rsFormFundTypes = Nothing

End Function

'*-----------------------------------------------------------------------
'* Method:      GetFundTypeByID
'* Parameters:  strFundID - Fund ID
'* Returns:     Fund type for the specified fund
'* Description: This method will find the fund type for a specific fund, specified by FUND ID
'*-----------------------------------------------------------------------

Public Function GetFundNameAndType(ByVal nTransferFundID As String, ByRef sTransferFundName, ByRef sTransferFundType) As String
    

Dim adoConn As ADODB.Connection
Dim rsFundDetails As ADODB.recordSet
Dim adoComm As ADODB.Command






Set adoConn = New ADODB.Connection
With adoConn
    .CursorLocation = adUseClient
    .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
    .Open
End With

Set rsFundDetails = New ADODB.recordSet
With rsFundDetails
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
End With

Set adoComm = New ADODB.Command
With adoComm
    .CommandText = "dir_get_Fund_ID_details"
    .CommandType = adCmdStoredProc
    Set .ActiveConnection = adoConn
    .Parameters.Append .CreateParameter("fund_id", adInteger, adParamInput, 4, nTransferFundID)
End With
    
Set rsFundDetails = adoComm.Execute
        
    If rsFundDetails.EOF Then
        GetFundNameAndType = "No details were found for the transfer fund you specified."
    Else
        sTransferFundName = rsFundDetails.Fields("name").Value
        sTransferFundType = rsFundDetails.Fields("scod_fund_type").Value
    End If
    
    
            
    'clean up
    rsFundDetails.Close
    Set rsFundDetails = Nothing
    adoConn.Close
    Set adoConn = Nothing
        
End Function

Public Function InsertFundPAYERefNum(ByVal sFundNumber As String, _
                            ByVal nFundPAYERefNumber As String) As String

    Dim cnInsertFundPAYE As ADODB.Connection
    Dim rsFundPAYE As ADODB.recordSet
    
    Dim rsHolder As ADODB.recordSet
    Dim nSeqNo As Double
    Dim cmd As ADODB.Command
    Dim strFundPAYE As String
    
    Dim ConnHolder As Object
    
    Dim obj_ADOConn As ADODB.Connection
    Dim strSQL As String
    
    Dim nFundID As Double
    
    'Tina : 2004/03/19 new SARS change
    '-----------------------------------------------------------------------------------
    'open the connection to the database
    Set obj_ADOConn = New ADODB.Connection
    obj_ADOConn.ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
    obj_ADOConn.Open
    
    'Set Objects
    Set rsFundPAYE = New ADODB.recordSet
    
    strSQL = "select distinct fund_paye_ref_num from dir_funds where fund_number = '" & sFundNumber & "'"
    rsFundPAYE.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
    If IsNull(rsFundPAYE.Fields("fund_paye_ref_num").Value) = False Then
        strFundPAYE = rsFundPAYE("fund_paye_ref_num")
    Else
        strFundPAYE = ""
    End If
    rsFundPAYE.Close
    

    '--------------------------------------------------------------------------------------
    If strFundPAYE = "" And nFundPAYERefNumber <> "" Then
    
        Set cnInsertFundPAYE = New ADODB.Connection
        
        
        Set cmd = New ADODB.Command 'CreateObject("ADODB.COMMAND")
        
        With cnInsertFundPAYE
            .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
            .Open
        End With
        
        Set rsHolder = New ADODB.recordSet
        '-------------------------------------------
        Set cmd.ActiveConnection = cnInsertFundPAYE
        cmd.CommandText = "dir_insert_fund_paye_ref_num"
        cmd.CommandType = adCmdStoredProc
               
            cmd.Parameters.Append cmd.CreateParameter("fund_number", adDouble, adParamInput, 11, IIf(sFundNumber = "", Null, sFundNumber))
        
            cmd.Parameters.Append cmd.CreateParameter("fund_paye_ref_num", adDouble, adParamInput, 11, IIf(nFundPAYERefNumber = "", Null, nFundPAYERefNumber))
            
        cmd.Execute
    End If
    
PROC_EXIT:
    obj_ADOConn.Close
    Set obj_ADOConn = Nothing
    Set rsFundPAYE = Nothing
  Exit Function
  
PROC_ERROR:
  nSeqNo = 0
  Err.Raise Err.Number, "InsertFundPAYERefNum", _
            "The fund paye ref number could not be added." & vbCrLf & _
            "Description: " & Err.Description
  GoTo PROC_EXIT

'clean up
cnInsertFundPAYE.Close
Set cnInsertFundPAYE = Nothing
rsHolder.Close
Set rsHolder = Nothing

End Function

'Created by:    Johann Marx
'Created on:    2007/11/07
'Description:   Returns the PAYE reference number for a fund. If no number is found for the fund, the ref number for the
'               business unit will be returned.
Public Function GetFundPAYERefNumber(ByVal nFundNumber As String, _
                                     ByVal sUserName As String) As String

On Error GoTo PROC_ERROR

    Dim sReturnValue As String
    Dim sFundPAYRefNumber As String
    Dim sBusUnitPAYERefNumber As String
    
    Dim objFundDetails As ClsFundDetail
    Set objFundDetails = New ClsFundDetail
    
    '--user details---------------------------
    Dim objUserDetails As clsUserDetails
    Dim rsUserDetails As ADODB.recordSet
    Dim nUserCompanyID As Integer
    Dim sUserBusinessCode As String
    
    Set objUserDetails = New clsUserDetails
    '-----------------------------------------
    
    '--business unit details------------------
    Dim objBusinessUnits As clsBusinessUnits
    Set objBusinessUnits = New clsBusinessUnits
    '-----------------------------------------
    
    sFundPAYRefNumber = objFundDetails.GetFundPAYEDetails(nFundNumber)
    
    'if we didn't get a PAYE Ref number back, and a username was specified, get the PAYE Ref number for the business unit
    If Len(sFundPAYRefNumber) = 0 And Len(sUserName) > 0 Then
        Set rsUserDetails = objUserDetails.GetUserDetailsRS(sUserName)
        
        If Not rsUserDetails.EOF Then
            nUserCompanyID = rsUserDetails.Fields("COM_ID").Value
            If Not IsNull(rsUserDetails.Fields("BUNI_CODE").Value) Then sUserBusinessCode = rsUserDetails.Fields("BUNI_CODE").Value
            
            'Only do if the user is linked to a business unit
            If Len(nUserCompanyID) > 0 And Len(sUserBusinessCode) > 0 Then
                sBusUnitPAYERefNumber = objBusinessUnits.GetPAYERefNumber(CStr(nUserCompanyID), sUserBusinessCode)
            End If
        End If
    End If
    
    'Set up the return value
    If Len(sFundPAYRefNumber) > 0 Then
        sReturnValue = sFundPAYRefNumber
    ElseIf Len(sBusUnitPAYERefNumber) > 0 Then
        sReturnValue = sBusUnitPAYERefNumber
    Else
        sReturnValue = ""
    End If
    
PROC_EXIT:
    'clean up
    Set objFundDetails = Nothing
    Set objUserDetails = Nothing
    Set objBusinessUnits = Nothing
    Set rsUserDetails = Nothing
    
    
    GetFundPAYERefNumber = sReturnValue
    Exit Function
    
PROC_ERROR:
    sReturnValue = ""
    GoTo PROC_EXIT
    
End Function
